package com.batchengine.db;

/**
 * Author  : SunilKalva
 * User     : skalva@ymail.com
 * Date     : Apr 5, 2008
 * <p/>
 * Copyright (c) 2007 Incubator Incorporated. All Rights Reserved.
 * <p/>
 * This software is the confidential and proprietary information of
 * Incubator, Inc. Use is subject to license terms.
 */

import com.batchengine.db.handlers.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;

/**
 * A Java Bean based on Commons DbUtils
 *
 * @author e-surfer
 */
public class DBConnectionUtil {

    private static Context initCtx = null;
    private static DataSource ds = null;
    private static String resource = "java:comp/env/jdbc/Test";

    /**
     * Get a connection from pool
     *
     * @return a Connection
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            if (initCtx == null) {
                initCtx = new InitialContext();
            }
            if (ds == null) {
                ds = (DataSource) initCtx.lookup(resource);
            }
            try {
                conn = ds.getConnection();
            } catch (SQLException ignored) {
            }
        } catch (NamingException ignored) {
        }
        return conn;
    }

    /**
     * Look up the DataSource
     *
     * @return the DataSource
     */
    public static DataSource getDataSource() {
        try {
            if (initCtx == null) {
                initCtx = new InitialContext();
            }
            if (ds == null) {
                ds = (DataSource) initCtx.lookup(resource);
            }
        } catch (NamingException ignored) {
        }
        return ds;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters and
     * place the column values from the first row in an Object[].
     * <p/>
     * Usage Demo:
     * <pre>
     *      Object[] result = searchToArray(sql);
     *      if (result != null) {
     *          for (int i = 0; i < result.length; i++) {
     *              System.out.println(result[i]);
     *          }
     *      }
     * </pre>
     *
     * @param sql The SQL to execute.
     * @return An Object[] or null if there are no rows in the ResultSet.
     */
    public static Object[] searchToArray(String sql) {
        Object[] result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new ArrayHandler();
            result = (Object[]) run.query(sql, h);
        } catch (Exception ignored) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter and
     * place the column values from the first row in an Object[].
     *
     * @param sql   The SQL statement to execute.
     * @param param The replacement parameter.
     * @return An Object[] or null if there are no rows in the ResultSet.
     */
    public static Object[] searchToArray(String sql, Object param) {
        Object[] result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new ArrayHandler();
            result = (Object[]) run.query(sql, param, h);
        } catch (Exception ignored) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL query and place the
     * column values from the first row in an Object[].
     *
     * @param sql    The SQL statement to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return An Object[] or null if there are no rows in the ResultSet.
     */
    public static Object[] searchToArray(String sql, Object[] params) {
        Object[] result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new ArrayHandler();
            result = (Object[]) run.query(sql, params, h);
        } catch (Exception ignored) {
        }
        return result;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters and
     * place the ResultSet into a List of Object[]s
     * <p/>
     * Usage Demo:
     * <pre>
     *      ArrayList result = searchToArrayList(sql);
     *      Iterator iterator = result.iterator();
     *      while (iterator.hasNext()) {
     *          Object[] temp = (Object[])iterator.next();
     *          for (int i = 0; i < temp.length; i++) {
     *              System.out.println(temp[i]);
     *          }
     *      }
     * </pre>
     *
     * @param sql The SQL statement to execute.
     * @return A List of Object[]s, never null.
     */
    public static ArrayList searchToArrayList(String sql) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new ArrayListHandler();
            result = (ArrayList) run.query(sql, h);
        } catch (Exception ignored) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and place the ResultSet into a List of Object[]s
     *
     * @param sql   The SQL statement to execute.
     * @param param The replacement parameter.
     * @return A List of Object[]s, never null.
     */
    public static ArrayList searchToArrayList(String sql, Object param) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new ArrayListHandler();
            result = (ArrayList) run.query(sql, param, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL query and place
     * the ResultSet into a List of Object[]s
     *
     * @param sql    The SQL statement to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A List of Object[]s, never null.
     */
    public static ArrayList searchToArrayList(String sql, Object[] params) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new ArrayListHandler();
            result = (ArrayList) run.query(sql, params, h);
        } catch (Exception ignored) {
        }
        return result;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and converts the first ResultSet into a Map object.
     * <p/>
     * Usage Demo:
     * <pre>
     *      Map result = searchToMap(sql);
     *      System.out.println(map.get(columnName));
     * </pre>
     *
     * @param sql The SQL to execute.
     * @return A Map with the values from the first row or null if there
     *         are no rows in the ResultSet.
     */
    public static Map searchToMap(String sql) {
        Map result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new MapHandler();
            result = (Map) run.query(sql, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and converts the first ResultSet into a Map object.
     *
     * @param sql   The SQL to execute.
     * @param param The replacement parameter.
     * @return A Map with the values from the first row or null if there
     *         are no rows in the ResultSet.
     */
    public static Map searchToMap(String sql, Object param) {
        Map result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new MapHandler();
            result = (Map) run.query(sql, param, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL query and converts
     * the first ResultSet into a Map object.
     *
     * @param sql    The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A Map with the values from the first row or null if there
     *         are no rows in the ResultSet.
     */
    public static Map searchToMap(String sql, Object[] params) {
        Map result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new MapHandler();
            result = (Map) run.query(sql, params, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and converts the ResultSet into a List of Map objects.
     * <p/>
     * Usage Demo:
     * <pre>
     *      ArrayList result = searchToMapList(sql);
     *      Iterator iterator = result.iterator();
     *      while (iterator.hasNext()) {
     *           Map map = (Map)iterator.next();
     *           System.out.println(map.get(columnName));
     *      }
     * </pre>
     *
     * @param sql The SQL to execute.
     * @return A List of Maps, never null.
     */
    public static ArrayList searchToMapList(String sql) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new MapListHandler();
            result = (ArrayList) run.query(sql, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and converts the ResultSet into a List of Map objects.
     *
     * @param sql   The SQL to execute.
     * @param param The replacement parameter.
     * @return A List of Maps, never null.
     */
    public static ArrayList searchToMapList(String sql, Object param) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new MapListHandler();
            result = (ArrayList) run.query(sql, param, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL query and converts
     * the ResultSet into a List of Map objects.
     *
     * @param sql    The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A List of Maps, never null.
     */
    public static ArrayList searchToMapList(String sql, Object[] params) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new MapListHandler();
            result = (ArrayList) run.query(sql, params, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and Convert the first row of the ResultSet into a bean with the
     * Class given in the parameter.
     * <p/>
     * Usage Demo:
     * <pre>
     *      String sql = "SELECT * FROM test";
     *      Test test = (Test)searchToBean(Test.class, sql);
     *      if (test != null) {
     *          System.out.println("test:" + test.getPropertyName());
     *      }
     * </pre>
     *
     * @param type The Class of beans.
     * @param sql  The SQL to execute.
     * @return An initialized JavaBean or null if there were no rows in
     *         the ResultSet.
     */
    public static Object searchToBean(Class type, String sql) {
        Object result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new BeanHandler(type);
            result = run.query(sql, h);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and Convert the first row of the ResultSet into a bean with the
     * Class given in the parameter.
     *
     * @param type  The Class of beans.
     * @param sql   The SQL to execute.
     * @param param The replacement parameter.
     * @return An initialized JavaBean or null if there were no rows in
     *         the ResultSet.
     */
    public static Object searchToBean(Class type, String sql, Object param) {
        Object result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new BeanHandler(type);
            result = run.query(sql, param, h);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL query and Convert the first row of
     * the ResultSet into a bean with the Class given in the parameter.
     *
     * @param type   The Class of beans.
     * @param sql    The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return An initialized JavaBean or null if there were no rows in
     *         the ResultSet.
     */
    public static Object searchToBean(Class type, String sql, Object[] params) {
        Object result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new BeanHandler(type);
            result = run.query(sql, params, h);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters
     * and Convert the ResultSet rows into a List of beans with the
     * Class given in the parameter.
     * <p/>
     * Usage Demo:
     * <pre>
     *      ArrayList result = searchToBeanList(Test.class, sql);
     *      Iterator iterator = result.iterator();
     *      while (iterator.hasNext()) {
     *           Test test = (Test)iterator.next();
     *           System.out.println(test.getPropertyName());
     *      }
     * </pre>
     *
     * @param type The Class that objects returned from handle() are created from.
     * @param sql  The SQL to execute.
     * @return A List of beans (one for each row), never null.
     */
    public static ArrayList searchToBeanList(Class type, String sql) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new BeanListHandler(type);
            result = (ArrayList) run.query(sql, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL with a single replacement parameter
     * and Convert the ResultSet rows into a List of beans with the
     * Class given in the parameter.
     *
     * @param type  The Class that objects returned from handle() are created from.
     * @param sql   The SQL to execute.
     * @param param The replacement parameter.
     * @return A List of beans (one for each row), never null.
     */
    public static ArrayList searchToBeanList(Class type, String sql, Object param) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new BeanListHandler(type);
            result = (ArrayList) run.query(sql, param, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Executes the given SELECT SQL query and Convert the ResultSet rows
     * into a List of beans with the Class given in the parameter.
     *
     * @param type   The Class that objects returned from handle() are created from.
     * @param sql    The SQL to execute.
     * @param params Initialize the PreparedStatement's IN
     *               parameters with this array.
     * @return A List of beans (one for each row), never null.
     */
    public static ArrayList searchToBeanList(Class type, String sql, Object[] params) {
        ArrayList result = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            ResultSetHandler h = new BeanListHandler(type);
            result = (ArrayList) run.query(sql, params, h);
        } catch (Exception ex) {
        }
        return result;
    }

    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
     *
     * @param sql    The SQL to execute.
     * @param params An array of query replacement parameters. Each row
     *               in this array is one set of batch replacement values.
     * @return The number of rows updated per statement.
     */
    public static int[] batch(String sql, Object[][] params) {
        int[] rows = null;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            rows = run.batch(sql, params);
        } catch (Exception ex) {
        }
        return rows;
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without any
     * replacement parameters.
     *
     * @param sql The SQL statement to execute.
     * @return The number of rows updated.
     */
    public static int update(String sql) {
        int rows = 0;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            rows = run.update(sql);
        } catch (Exception ex) {
        }
        return rows;
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement with a single
     * replacement parameter.
     *
     * @param sql   The SQL statement to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     */
    public static int update(String sql, Object param) {
        int rows = 0;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            rows = run.update(sql, param);
        } catch (Exception ex) {
        }
        return rows;
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement.
     *
     * @param sql    The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?') parameters.
     * @return The number of rows updated.
     */
    public static int update(String sql, Object[] params) {
        int rows = 0;
        try {
            QueryRunner run = new QueryRunner(getDataSource());
            rows = run.update(sql, params);
        } catch (Exception ex) {
        }
        return rows;
    }

    /**
     * Get Maximun Field Value Named 'sFldName' of Table 'sTblName'
     *
     * @param sTblName The Table Name
     * @param sFldName The Field Name
     * @return Maximum Id
     */
    public static int getMaxId(String sTblName, String sFldName) {
        int iRes = 1;
        String sql = "SELECT MAX(" + sFldName + ") AS maxid FROM " + sTblName;
        Map result = searchToMap(sql);
        if (result.get("maxid") != null) {
            iRes = (Integer) result.get("maxid") + 1;
        }
        return iRes;
    }

    /**
     * Get total number of records
     *
     * @param sqlcnt sql statement used to count number of records
     *               for example: SELECT COUNT(*) AS cnt FROM demotable
     * @return total number of records
     */
    public static int getNumOfRec(String sqlcnt) {
        int iRes = 0;
        Map result = DBConnectionUtil.searchToMap(sqlcnt);
        if (result.get("cnt") != null) {
            iRes = (int) ((Long) result.get("cnt")).longValue();
        }
        return iRes;
    }
}